package com.p6spy.engine;

import com.p6spy.engine.config.DatabaseConfig;

import java.sql.*;

/**
 * 客户端测试
 */
public class Client {

    /**
     * 主函数，用于测试数据库操作
     *
     * @param args 命令行参数
     * @throws Exception 异常处理
     */
    public static void main(String[] args) throws Exception {

        // 定义SQL查询语句：查询学生姓名
        String selectSql = "select name from student where id = 1";
        // 定义预处理查询语句：查询学生地址（通过id参数）
        String selectSqlForPrepare = "select address from student where id = ?";
        // 定义预处理插入语句：向student表中插入数据
        String insertSqlForPrepare = "insert into student values(?, ?, 1, ?, ?, ?)";
        // 定义删除语句：删除指定id的学生记录
        String deleteSql = "delete from student where id = 3";
        // 定义错误的删除语句：尝试从不存在的表中删除记录
        String deleteSqlForError = "delete from not_exist_table where id = 2";

        try (
                // 获取数据库连接
                Connection con = DatabaseConfig.getConnection();

                // 创建Statement对象，用于执行SQL语句
                Statement statement = con.createStatement();

                // 创建预处理Statement对象，用于执行带有参数的查询语句
                PreparedStatement preparedStatementSelect = con.prepareStatement(selectSqlForPrepare);

                // 创建另一个预处理Statement对象，用于执行带有参数的插入语句
                PreparedStatement preparedStatementInsert = con.prepareStatement(insertSqlForPrepare)
        ) {
            // 1、执行查询并输出结果
            ResultSet resultSet1 = null;
            try {
                resultSet1 = statement.executeQuery(selectSql);
                System.out.println("selectSql执行结果：" + (resultSet1.next() ? resultSet1.getString(1) : null));
            } catch (SQLException e) {
                System.out.println("SQL执行异常信息：" + e.getMessage());
            }

            // 2、设置预处理查询参数并执行查询
            try {
                preparedStatementSelect.setInt(1, 4);
                preparedStatementSelect.executeQuery();
                ResultSet resultSet2 = preparedStatementSelect.executeQuery();
                System.out.println("preparedStatementSelect执行结果：" + (resultSet2.next() ? resultSet1.getString(1) : null));
            } catch (SQLException e) {
                System.out.println("SQL执行异常信息：" + e.getMessage());
            }

            // 3、设置预处理插入语句参数并执行插入操作
            try {
                preparedStatementInsert.setInt(1, 6);
                preparedStatementInsert.setString(2, "lcz");
                preparedStatementInsert.setString(3, "13800138000");
                preparedStatementInsert.setDate(4, new Date(2018, 12, 31));
                preparedStatementInsert.setString(5, "杭州市萧山区");
                boolean success1 = preparedStatementInsert.execute();
                System.out.println("insertSqlForPrepare执行结果：" + success1);
            } catch (SQLException e) {
                System.out.println("SQL执行异常信息：" + e.getMessage());
            }

            // 4、执行删除操作并输出结果
            try {
                boolean success2 = statement.execute(deleteSql);
                System.out.println("deleteSql执行结果：" + success2);
            } catch (SQLException e) {
                System.out.println("SQL执行异常信息：" + e.getMessage());
            }

            // 5、尝试执行错误的删除操作
            try {
                boolean success3 = statement.execute(deleteSqlForError);
                System.out.println("deleteSqlForError执行结果：" + success3);
            } catch (SQLException e) {
                System.out.println("SQL执行异常信息：" + e.getMessage());
            }

        } catch (Exception e) {
            // 输出异常信息
            System.out.println("异常信息：" + e.getMessage());
        }

    }


}
